package dao;

import classes.entidade.Orgao;
import conexao.GerenciadorConexao;
import erro.ConexaoException;
import erro.ConsultaException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author roberta.m.carvalho
 */
public class OrgaoDao {

    private GerenciadorConexao g;

    public OrgaoDao() {
        g = GerenciadorConexao.getInstancia();
    }

    public void inserirOrgao(Orgao o) throws ConexaoException, ConsultaException {
        String sql = "INSERT INTO orgao(nome,cnpj,responsavel) "
                + "VALUES (?,?,?)";
        try {
            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            pstm.setString(1, o.getNome());
            pstm.setString(2, o.getCnpj());
            pstm.setString(3, o.getResponsavel());
            pstm.executeUpdate();
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
    }

    public void alterarOrgao(Orgao o) throws ConexaoException, ConsultaException {
        String sql = "UPDATE orgao SET nome=?,cnpj=?,responsavel=? WHERE idorgao=?";
        try {
            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            pstm.setString(1, o.getNome());
            pstm.setString(2, o.getCnpj());
            pstm.setString(3, o.getResponsavel());
            pstm.setInt(4, o.getIdOrgao());
            pstm.executeUpdate();
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
    }

    public ArrayList<Orgao> listarOrgao() throws ConexaoException, ConsultaException {
        ArrayList<Orgao> lista = new ArrayList<Orgao>();
        Orgao orgao;
        String sql = "SELECT * "
                + "FROM orgao order by nome asc";
        try {
            Statement stm = (Statement) g.connect().createStatement();
            ResultSet rs = stm.executeQuery(sql);
            while (rs.next()) {
                orgao = new Orgao();
                orgao.setIdOrgao(rs.getInt("idorgao"));
                orgao.setNome(rs.getString("nome"));
                orgao.setCnpj(rs.getString("cnpj"));
                orgao.setResponsavel(rs.getString("responsavel"));
                lista.add(orgao);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return lista;
    }

    public void deletarOrgao(Orgao o) throws ConexaoException, ConsultaException {
        String sql = "DELETE FROM orgao where idorgao =?";
        try {
            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            pstm.setInt(1, o.getIdOrgao());
            pstm.executeUpdate();
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
    }
}
